package com.kfd.daoImpl;

import com.kfd.bean.Express;
import com.kfd.bean.lanren;
import com.kfd.dao.ExpressDao;
import com.kfd.exception.DuplicateCodeException;
import com.kfd.exception.DuplicateNumberException;
import com.kfd.util.JDBCutil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExpressDaoImpl extends JDBCutil implements ExpressDao {
//    返回控制台需要打印的内容（总快递数+新增）（代取件数+新增）
    private static final String SQL_CONSOLE = "SELECT COUNT(ID) data1_size,COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) OR NULL) data1_day,COUNT(STATUS=0 OR NULL) data2_size,COUNT(TO_DAYS(INTIME)=TO_DAYS(NOW()) AND STATUS=0 OR NULL) data2_day FROM EXPRESS ";
//    用于查询数据库中所有快递信息
    private static final String SQL_FIND_ALL = "SELECT * FROM EXPRESS";
//    用于分页查询快递信息
    private static final String SQL_FIND_LIMIT = "SELECT * FROM EXPRESS LIMIT ?,?";
//    根据快递单号查询快递信息
    private static final String SQL_FIND_BY_NUMBER = "SELECT * FROM EXPRESS WHERE NUMBER=?";
//    根据取件码查询快递信息
    private static final String SQL_FIND_BY_CODE = "SELECT * FROM EXPRESS WHERE CODE=?";
//    根据用户手机号查询快递信息
    private static final String SQL_FIND_BY_USER_PHONE = "SELECT * FROM EXPRESS WHERE USERPHONE=?";
//    根据用户手机号查询快递信息,根据status判断查询范围
    private static final String SQL_FIND_BY_USER_PHONE_STATUS = "SELECT * FROM EXPRESS WHERE USERPHONE=? AND STATUS=?";
//    根据快递员手机号查询快递信息
    private static final String SQL_FIND_BY_SYS_PHONE = "SELECT * FROM EXPRESS WHERE SYSPHONE=?";
//    快递录入
    private static final String SQL_INSERT = "INSERT INTO EXPRESS(NUMBER,USERNAME,USERPHONE,COMPANY,CODE,INTIME,STATUS,SYSPHONE) VALUES(?,?,?,?,?,NOW(),0,?)";
//    快递修改
    private static final String SQL_UPDATE = "UPDATE EXPRESS SET NUMBER=?,USERNAME=?,COMPANY=?,STATUS=? WHERE ID=?";
//    快递取出
    private static final String SQL_UPDATE_STATUS = "UPDATE EXPRESS SET CODE=NULL,STATUS=1,OUTTIME=NOW() WHERE CODE=?";
//    快递删除
    private static final String SQL_DELETE = "DELETE FROM EXPRESS WHERE ID=?";
//    单号重复验证
    private static final String SQL_NUMBER_REPETITION = "SELECT ID FROM EXPRESS WHERE NUMBER=?";
//    取件码重复验证
    private static final String SQL_CODE_REPETITION = "SELECT ID FROM EXPRESS WHERE CODE=?";
    //    懒人排行
    private static final String SQL_FIND_LAN = "SELECT USERNAME AS `NAME`,COUNT(USERPHONE) AS QUANTITY FROM EXPRESS ";


    /**
     * 返回控制台需要打印的内容（总快递数+新增）（代取件数+新增）
     *
     * @return 返回2条数据0（总快递数+新增）；1（代取件数+新增）
     */
    @Override
    public List<Map<String, Integer>> console() {
        List<Map<String, Integer>> list = new ArrayList<>();

        ResultSet select = select(SQL_CONSOLE, null);
        try {
            if(select.next()){
                int data1_size = select.getInt("data1_size");
                int data1_day = select.getInt("data1_day");
                int data2_size = select.getInt("data2_size");
                int data2_day = select.getInt("data2_day");
                Map<String,Integer> map1 = new HashMap<String,Integer>();
                map1.put("data1_size",data1_size);
                map1.put("data1_day",data1_day);
                Map<String,Integer> map2 = new HashMap<String,Integer>();
                map2.put("data2_size",data2_size);
                map2.put("data2_day",data2_day);
                list.add(map1);
                list.add(map2);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        if(list.size()==0)
            return null;
        return list;
    }

    /**
     * 查询所有快递
     *
     * @param limit      是否开启分页；true表示开启分页
     * @param offset     sql语句起始下标
     * @param pageNumber 共查询多少条
     * @return 快递的集合
     */
    @Override
    public List<Express> findAll(boolean limit, int offset, int pageNumber) {
        List<Express> list;
        if(limit){
            //为占位符赋值
            list = selectQueryAll(select(SQL_FIND_LIMIT,new ArrayList(){{add(offset);add(pageNumber);}}), Express.class, null);
        }else{
             list = selectQueryAll(select(SQL_FIND_ALL, null), Express.class, null);
        }
        close();
        if(list == null || list.size()==0)
            return null;
        return list;
    }

    /**
     * 根据快递单号查询快递信息
     *
     * @param number 单号
     * @return 查询到的快递信息，有可能为空返回null，单号不存在
     */
    @Override
    public Express findByNumber(String number) {
        List<Express> list = selectQueryAll(select(SQL_FIND_BY_NUMBER, new ArrayList() {{
            add(number);
        }}), Express.class, null);
        close();
        if(list == null || list.size()==0)
            return null;
        return list.get(0);
    }

    /**
     * 根据取件码查询快递信息
     *
     * @param code 取件码
     * @return 查询到的快递信息，有可能为空返回null，取件码不存在
     */
    @Override
    public Express findByCode(String code) {
        List<Express> list = selectQueryAll(select(SQL_FIND_BY_CODE, new ArrayList() {{
            add(code);
        }}), Express.class, null);
        close();
        if(list == null || list.size()==0)
            return null;
        return list.get(0);
    }

    /**
     * 根据用户手机号查询快递信息
     *
     * @param userPhone 用户手机号
     * @param status    表示查询的快递类型 [0、表示查询待取件的快递；1、表示查询已取件的快递；2、表示查询所有]
     * @return 查询到的快递信息，有可能为空返回null，手机号不存在快递
     */
    @Override
    public List<Express> findByUserPhone(String userPhone, int status) {
        List<Express> list = null;
        if(2 == status){
             list = selectQueryAll(select(SQL_FIND_BY_USER_PHONE, new ArrayList() {{
                add(userPhone);
            }}), Express.class, null);
        }else if(1 == status){
            list = selectQueryAll(select(SQL_FIND_BY_USER_PHONE_STATUS, new ArrayList() {{
                add(userPhone);add("1");
            }}), Express.class, null);
        }else if(0 == status){
            list = selectQueryAll(select(SQL_FIND_BY_USER_PHONE_STATUS, new ArrayList() {{
                add(userPhone);add("0");
            }}), Express.class, null);
        }
        close();
        if(list == null || list.size()==0)
            return null;
        return list;
    }

    /**
     * 根据快递员手机号查询快递信息
     *
     * @param sysPhone 快递员手机号
     * @return 查询到的快递信息，有可能为空返回null，手机号不存在快递
     */
    @Override
    public List<Express> findBySysPhone(String sysPhone) {
        List<Express> list = selectQueryAll(select(SQL_FIND_BY_SYS_PHONE, new ArrayList() {{
            add(sysPhone);
        }}), Express.class, null);
        close();
        if(list == null || list.size()==0)
            return null;
        return list;
    }

    /**
     * 根据快递员手机号查询快递信息，查询快递取件量，未取件量
     *
     * @param sysPhone 快递员手机号
     * @param status 快递信息
     * @return 查询到的快递信息，有可能为空返回null，手机号不存在快递
     */
    @Override
    public List<Express> findBySysPhoneNo(String sysPhone,String status) {
        List<Express> list = selectQueryAll(select(SQL_FIND_BY_SYS_PHONE+" AND STATUS =?", new ArrayList() {{
            add(sysPhone);
            add(status);
        }}), Express.class, null);
        close();
        if(list == null || list.size()==0)
            return null;
        return list;
    }

    /**
     * 快递的录入
     *
     * @param express 快递信息
     * @return 返回是否录入成功
     */
    @Override
    public boolean insert(Express express) throws DuplicateCodeException, DuplicateNumberException {
        int update  = update(SQL_INSERT, getList(express, new ArrayList() {{
                add("NUMBER");
                add("USERNAME");
                add("USERPHONE");
                add("COMPANY");
                add("CODE");
                add("SYSPHONE");
            }}));
        close();
        return update<=0?false:true;
    }

    /**
     * 快递的修改
     *
     * @param express 快递信息，id不变修改其他值（number,company,username,userphone）
     * @return 返回是否修改成功
     */
    @Override
    public boolean update(Express express) {
        int update = 0;
        try {
            update = update(SQL_UPDATE, getList(express, new ArrayList() {{
                add("NUMBER");
                add("USERNAME");
                add("COMPANY");
                add("STATUS");
                add("ID");
            }}));
        } catch (DuplicateNumberException | DuplicateCodeException e) {
            System.out.println(e.getMessage());
        }
        close();
        return update<=0?false:true;
    }

    /**
     * 快递取件；更改快递的状态为1，表示已取件,同时取件码失效，取件时间自动更新
     *
     * @param code 快递取件码
     * @return 返回是否修改成功
     */
    @Override
    public boolean updateStatus(String code) {
        int update = 0;
        try {
            update = update(SQL_UPDATE_STATUS,new ArrayList(){{
                add(code);
            }});
        } catch (DuplicateNumberException | DuplicateCodeException e) {
            e.printStackTrace();
        }
        close();
        return update<=0?false:true;
    }

    /**
     * 快递的删除
     *
     * @param id 快递的id
     * @return 返回是否删除成功
     */
    @Override
    public boolean delete(int id) {
        int update = 0;
        try {
            update = update(SQL_DELETE,new ArrayList(){{
                add(id);
            }});
        } catch (DuplicateNumberException | DuplicateCodeException e) {
            e.printStackTrace();
        }
        close();
        return update<=0?false:true;
    }

    /**
     * 快递单号是否重复验证
     *
     * @param number 快递单号
     * @return true是不重复，false重复需要重新输入
     */
    @Override
    public boolean numberRepetition(String number) {
        ResultSet select = select(SQL_NUMBER_REPETITION, new ArrayList() {{
            add(number);
        }});
        close();
        try {
            return select.next()?false:true;
        } catch (SQLException e) {
            return false;
        }
    }

    /**
     * 取件码重复验证，
     *
     * @param code 随机生成的取件码
     * @return true是不重复，false重复需要重新生成
     */
    @Override
    public boolean codeRepetition(String code) {
        ResultSet select = select(SQL_CODE_REPETITION, new ArrayList() {{
            add(code);
        }});
        close();
        try {
            return select.next()?false:true;
        } catch (SQLException e) {
            return false;
        }
    }

    /**
     * 查询总的懒人排行
     *
     * @return
     */
    @Override
    public List<lanren> getLanlist(String year,String month) {
        ResultSet select = null;
        if(year.indexOf("null") == -1){
            select = select(SQL_FIND_LAN+"WHERE INTIME LIKE "+year+" GROUP BY NAME ORDER BY QUANTITY DESC;", null);
        }else if(month.indexOf("null") == -1){
            select = select(SQL_FIND_LAN+"WHERE INTIME LIKE "+month+" GROUP BY NAME ORDER BY QUANTITY DESC;", null);
        }else{
            select = select(SQL_FIND_LAN+"GROUP BY USERPHONE ORDER BY QUANTITY DESC;", null);
        }
        List list = selectQueryAll(select, lanren.class, null);

        close();
//        List list = new ArrayList();
//        try {
//            while (select.next()){
//                String username = select.getString("username");
//                if (username == null) {
//                    list.add("");
//                } else {
//                    list.add(username);
//                }
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
        return list;
//
//        select username,count(userphone) as sta from express where intime LIKE "2020%" GROUP BY userphone ORDER BY sta DESC;
//
//        select username,count(userphone) as sta from express where intime LIKE "_____10%" GROUP BY userphone ORDER BY sta DESC;
    }
}
